Loading the Fact table 12

ยท                 Create a new Package to Load Fact Table. Right Click on SSIS Packages ---- > Click New SSIS Package


ยท                     New Package will appear in Solution Explorer. Rename by Right click on Package name ---- > Click Rename --- > Type Fact_Mortgage ----- > Click Enter.


                  ยท                     Drag and Drop Execute SQL Task from Favorites in Control Flow

 

                  ยท                     Double Click on Execute SQL task. Execute SQL task Editor will pop up.

ยท                     In Connection Click Drop Down arrow ---- > Click New Connection          

 ยท                  Choose the Destination database (Mortgage Datamart)  and Click Ok


                 ยท                   In SQL Statement ---- > Use the Code

TRUNCATE           TABLE     Fact_Mortgage

ยท               ยท                  Just copy and Paste the code in SQL Statement ---- > Click Ok

 

ยท                     Drag and Drop Data flow Task from Favorites below Execute SQL Task and Provide link between

                    two just by Dragging the Green line from Execute SQL task and place it on Top of Data flow task


                  ยท                     Double Click on Data flow Task 

                  

                 ยท                  Drag and Drop OLE DB Source Connection from Other sources

 

 ยท              Double Click on OLE DB Source ---- > Click New


                    ยท                  Another Editor will pop up. In that Choose the Database name (Colaberry Database) ----- > Click OK
        

                  ยท                 Click on the Drop down of Data access Mode ---- > Choose SQL Command


ยท                Copy and Paste the code in SQL Command Text ---- > Click Columns Script to Copy: 

 SELECT   Distinct [ContractID],[MortgageID],[PropertyID],[OwnerID],[PropertyTotalPrice],
[DownPayment],[BalanceDue],[AmountFinanced],[Term],[NumberOfPayments],[PaymentsRemaining],[APR],

 [MonthlyPayment],[FinalPrice],[ProjectedProfit],[AmountRecovered],[BalanceRemaining],

 [MaintenanceFee],[ARBalance],[ContractPointBalance]

 FROM  [ColaberryDatabase].[dbo].[MortgageStagingView]


ยท                  Drag and Drop Lookup and provide connection by dragging Blue arrow from OLE DB Source and  
                  Place it on Top of Lookup


ยท                Double Click on Lookup. Another Wizard will pop up.

ยท             
    Click on Drop down of Specify how to handle rows with no matching entries ---- > Choose 
                 Redirect row to no match output Click Connection


ยท                  Lookup Transformation editor will pop up. Click on Drop down of Connection Manager and    
   
               Choose destination Database  (Mortgage Datamart) ---- > Click OK

ยท              Choose Use results of an SQL Query ---- > Copy and Paste the Code

                                Script to use in Use results of an SQL Query:

SELECT  ContractID, ContractKey

FROM   Dim_Contract 

ยท                  Click Connection


ยท               
 Another Editor with Columns will Pop up. Drag ContractID from input columns and Drop it on 
                Lookup ContractID ----- > Click the Checkbox of ContractKey
ยท                 Change the Alias name to ContractKey_LKP
ยท                 Click OK              

                  


|

ยท                 
Drag and Drop Lookup below look up 


ยท                 Provide connection by dragging Blue line and place it on Lookup. Another wizard will pop up.

 Click on Drop down of Output and Choose Lookup Match Output

ยท                 Click OK


ยท                
  Right Click on Lookup 1 ---- > Click Rename ---- > Type Lookup โ€“ Dim_Mortgage


ยท                 Double click on Lookup โ€“ Dim_Mortgage 


ยท                    Click on Drop down of Specify how to handle rows with no matching entries ----- > Choose Rows with 
                   no match output
ยท                   Click Connection


ยท                 Another editor will pop up. Click on the Drop down of OLE DB Connection Manager ---- > Choose
                Destination Database (Mortgage Datamart) ---- > Click OK

ยท                
  Choose Use results of an SQL Query
ยท                  Copy and Paste the Code in Use results of an SQL Query
Script to paste:

SELECT         MortgageID, MortgageKey

FROM            Dim_Mortgage


ยท              Another Editor will Pop up. Drag MortgageID from Input Column and Drag it on Lookup MortgageID
ยท              Click the Checkbox of MortgageKey in Lookup Column
ยท               Change Output Alias name to MortgageKey_LKP. Click OK

                


                

ยท          Drag and Drop Lookup below Lookup โ€“ Dim_Mortgage


ยท         Provide connection by dragging Blue line and place it on Lookup. Another wizard will pop up.
ยท         Click on Drop down of Output and Choose Lookup Match Output
ยท         Click OK


ยท                     
Right Click on Lookup 1 ---- > Click Rename ---- > Type Lookup โ€“ Dim_Owner

ยท         
Double click on Lookup โ€“ Dim_Owner


ยท         
Click on Drop down of Specify how to handle rows with no matching entries ----- > Choose 
          Rows with no match output
ยท         Click Connection


ยท         
Another editor will pop up. Click on the Drop down of OLE DB Connection Manager ---- > Choose    
 
         Destination Database (Mortgage Datamart) ---- > Click OK


ยท         
Choose Use results of an SQL Query
ยท         Copy and Paste the Code in Use results of an SQL Query

Script to paste:

SELECT   OwnerID, OwnerKey

FROM     Dim_Owner


ยท         
Another Editor will Pop up. Drag OwnerID  from Input Column and Drag it on Lookup OwnerID
ยท         Click the checkbox of OwnerKey in Lookup Column
ยท         Change Output Alias name to OwnerKey_LKP. Click OK
        


ยท                   
Drag and Drop Lookup below Lookup โ€“ Dim_Owner


ยท         
 Provide connection by dragging Blue line and place it on Lookup. Another wizard will pop up.
ยท         Click on Drop down of Output and Choose Lookup Match Output
ยท         Click OK


ยท                    
Right Click on Lookup 1 ---- > Click Rename ---- > Type Lookup โ€“ Dim_Property


ยท                   
Double click on Lookup โ€“ Dim_Property


ยท                  
Click on Drop down of Specify how to handle rows with no matching entries ----- > Choose 
                  Rows with no match output Click Connection


ยท                  
Another editor will pop up. Click on the Drop down of OLE DB Connection Manager ---- > Choose 
                 Destination Database (Mortgage Datamart) ---- > Click OK


ยท                  
Choose Use results of an SQL Query

ยท                  Copy and Paste the Code in Use results of an SQL Query

Script to paste:

SELECT    PropertyID, PropertyKey

FROM     Dim_Property


ยท                     
Another Editor will Pop up. Drag PropertyID from Input Column and Drag it on Lookup PropertyID
ยท                     Click the checkbox of  PropertyKey in Lookup Column
ยท                     Change Output Alias name to PropertyKey _LKP. Click OK

                           



ยท                   
Drag and Drop OLE DB Destination from Other Destinations


ยท                   
Provide connection by dragging Blue line and place it on Lookup. Another wizard will pop up.
ยท                   Click on Drop down of Output and Choose Lookup Match Output
ยท                   Click OK


ยท                  
Double Click on OLE DB Destination 


ยท                   
Click on the Drop Down of OLE DB Connection Manager ---- > Choose Destination Database
 (Mortgage DataMart) ---- > Click OK


ยท                   
Click on the Drop down of Name of the table or view ---- > Choose destination Table (Fact_Mortgage)


ยท                  Click on the Drop Down of ignore at Input Columns (Make sure in


ยท                     
After matching Columns accordingly then click OK            




ยท                    
Package is error free and ready for execution as shown below

To load the Fact table

https://mindmajix.com/ssis/fact-table-loading